#!/bin/bash


#data_date=$1
add_sql="insert overwrite table jtp_oms_dwd.dwd_oms_order_incr partition (dt)
     select id,
            member_id,
            coupon_id,
            order_sn,
            create_time,
            total_amount,
            pay_amount,
            freight_amount,
            promotion_amount,
            integration_amount,
            coupon_amount,
            discount_amount,
            pay_type,
            pay_type_name,
            source_type,
            source_type_name,
            status,
            order_type,
            order_type_name,
            delivery_company,
            delivery_sn,
            integration,
            growth,
            confirm_status,
            delete_status,
            use_integration,
            payment_time,
            delivery_time,
            receive_time,
            comment_time,
            modify_time,
            dt
     from (select *,
                  row_number() over (partition by id order by modify_time desc) rk
           from (
                    select id,
                           member_id,
                           coupon_id,
                           order_sn,
                           create_time,
                           total_amount,
                           pay_amount,
                           freight_amount,
                           promotion_amount,
                           INTegration_amount,
                           coupon_amount,
                           discount_amount,
                           pay_type,
                           case
                               when pay_type = 0 then '未支付'
                               when pay_type = 1 then '支付宝'
                               when pay_type = 2 then '微信'
                               else cast(pay_type as string)
                               end                                as pay_type_name,
                           source_type,
                           case
                               when source_type = 0 then 'PC订单'
                               when source_type = 1 then 'app订单'
                               else cast(source_type as string)
                               end                                as source_type_name,
                           status,
                           order_type,
                           case
                               when order_type = 0 then '正常订单'
                               when order_type = 1 then '秒杀订单'
                               else cast(order_type as string)
                               end                                as order_type_name,
                           delivery_company,
                           delivery_sn,
                           INTegration,
                           growth,
                           confirm_status,
                           delete_status,
                           use_INTegration,
                           payment_time,
                           delivery_time,
                           receive_time,
                           comment_time,
                           modify_time,
                           date_format(create_time, 'yyyy-MM-dd') as dt
                    from jtp_oms_ods.ods_oms_order_incr
                    where dt = '${data_date}'
                    union all
                    select id,
                           member_id,
                           coupon_id,
                           order_sn,
                           create_time,
                           total_amount,
                           pay_amount,
                           freight_amount,
                           promotion_amount,
                           INTegration_amount,
                           coupon_amount,
                           discount_amount,
                           pay_type,
                           pay_type_name,
                           source_type,
                           source_type_name,
                           status,
                           order_type,
                           order_type_name,
                           delivery_company,
                           delivery_sn,
                           INTegration,
                           growth,
                           confirm_status,
                           delete_status,
                           use_INTegration,
                           payment_time,
                           delivery_time,
                           receive_time,
                           comment_time,
                           modify_time,
                           dt
                    from jtp_oms_dwd.dwd_oms_order_incr
                    where dt >= date_sub('${data_date}', 15)
                      and dt <= date_sub('${data_date}', 1)) t1) t2
     where rk = 1;
"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${add_sql}"